package edu.mju.dao.impl;

import edu.mju.dao.IOrderDao;
import edu.mju.dao.IUserDao;
import edu.mju.entity.*;
import edu.mju.service.IRoutesService;
import edu.mju.service.impl.RoutesServiceImpl;
import edu.mju.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wyp
 * @Date:2020/5/27 23:23
 */
public class OrderDaoImpl implements IOrderDao {
    Connection conn = null;
    PreparedStatement preparedStatement = null ;

    public PreparedStatement preparedStatement(String sql){
        try {
            conn  = MySqlDbUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("OrderDaoImpl>preparedStatement方法出错");
        }
        return preparedStatement;
    }

    @Override
    public boolean deleteOrder(Integer orderId) {
        boolean delete = false;
        try {
            String sql = "DELETE FROM UserOrder WHERE orderId=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setInt(1,orderId);
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public boolean insert(Order order) {
        boolean insert= false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "INSERT INTO UserOrder(userId,touristId,touristCompanyId) VALUES (?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,order.getUserId());
            pstmt.setInt(2,order.getTouristId());
            pstmt.setInt(3,order.getTouristCompanyId());
            int result =pstmt.executeUpdate();
            if(result>0){
                insert = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return insert;
    }

    @Override
    public Pager pagenate(int page, int pageSize, Integer touristId) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from UserOrder ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(touristId != null || touristId !=0) {

                where.append("AND touristId = ? ");
                params.add(touristId);
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from UserOrder ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            Order order ;
            while (rs.next()){
                order = new Order(rs.getInt("orderId"),rs.getInt("userId"),
                        rs.getInt("touristId"),rs.getInt("touristCompanyId"));

                IUserDao userDao = new UserDaoImpl();
                User user = userDao.findById(rs.getInt("userId"));


                data.add(order);
                data.add(user);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }

    @Override
    public Pager findByUserId(int page, int pageSize, Integer userId) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from UserOrder ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(userId != null || userId !=0) {

            where.append("AND userId = ? ");
            params.add(userId);
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from UserOrder ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            Order order ;
            while (rs.next()){
                order = new Order(rs.getInt("orderId"),rs.getInt("userId"),
                        rs.getInt("touristId"),rs.getInt("touristCompanyId"));

                IRoutesService routesService = new RoutesServiceImpl();
                TouristRoutes routes = routesService.findByRoutesId(rs.getInt("touristId"));


                data.add(order);
                data.add(routes);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }


}
